package org.sql.jdbc.dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.sql.jdbc.constants.DbTypeEnum;
import org.sql.jdbc.id.UniqueIdUtil;
import org.sql.jdbc.param.Operator;
import org.sql.jdbc.param.Param;
import org.sql.jdbc.utils.SqlUtil;

/** 
 * jdbc持久化操作层
 *
 * <pre> 
 * 构建组：e-jdbc
 * 作者：eddy
 * 邮箱：1546077710@qq.com
 * 日期：2017年4月23日-下午9:49:04
 * 版权：E源时代
 * </pre>
 */
public class JdbcBaseDao {
	
	/***
	 * 数据库连接
	 */
	private Connection conn;
	
	/**
	 * 预编译SQL语句对象
	 * */
	private PreparedStatement pstmt;
	
	/**
	 * SQL语句对象
	 * */
	private Statement stmt;
	
	private String dbType = DbTypeEnum.MYSQL.code();
	
	public Connection getConn(){
		return conn;
	}
	
	public String getDbType() {
		return dbType;
	}

	public void setDbType(String dbType) {
		this.dbType = dbType;
	}

	public void setConn(Connection conn){
		this.conn = conn;
		
		try {
			DatabaseMetaData daMe = conn.getMetaData();
			String ataName = daMe.getDatabaseProductName();
			dbType = DbTypeEnum.formCode(ataName).code();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 构造连接
	 */
	public JdbcBaseDao(Connection conn){
		this.conn = conn;
	}
	
	/**
	 * 新增持久化对象
	 *
	 * @param obj
	 * @return
	 * @throws Exception 
	 */
	public String insert(Object obj) throws Exception{
		if(null == obj){
			throw new Exception("对象为空！");
		}
		
		String pkValue = null;
		try {
			Operator operator = new Operator(obj);
			List<Param> fieldLis = operator.getFieldLis();
			
			String sql = SqlUtil.getInsertSql(operator);
			
			pstmt = conn.prepareStatement(sql);
			for(int i=0;i<fieldLis.size();i++){
				Param field = fieldLis.get(i);
			    Object value = field.getParamValue();
			    if(field.getParamType().equals("java.lang.Integer")){
			    	 pstmt.setInt(i+1,Integer.valueOf(value.toString()));
			    }else if(field.getParamType().equals("java.lang.Double")){
			    	 pstmt.setDouble(i+1,Double.valueOf(value.toString()));
			    }else if(field.getParamType().equals("java.util.Date")){
			    	pstmt.setDate(i+1,new java.sql.Date(((java.util.Date)value).getTime()));
			    }else if(field.getParamType().equals("java.sql.Date")){
			    	pstmt.setDate(i+1,(java.sql.Date)value);
			    }else if(field.getParamType().equals("java.lang.String")){
			    	pstmt.setString(i+1, value.toString());
			    }else{
			    	pstmt.setObject(i+1,value);
			    }
			}
			pstmt.executeUpdate();
			pkValue = operator.getPrikey().getParamValue();
		} catch (SQLException e) {
			throw new Exception("插入数据异常:"+e.getMessage(),e);
		} catch (Exception e) {
			throw new Exception("插入数据异常:"+e.getMessage(),e);
		}  finally {
			try {
				if(null != pstmt){					
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
		
		return pkValue;
	}
	
	/**
	 * 批量新增持久化对象
	 *
	 * @param objs
	 * @return
	 * @throws Exception 
	 */
	public int insertBatch(List<Object> objs) throws Exception{
		if(null == objs || 0 == objs.size()){
			throw new Exception("对象为空！");
		}
		
		int[] ins = new int[0];
		String pkValue = null;
		try {
			Operator operator = new Operator(objs.get(0));
			List<Param> fieldLis = operator.getFieldLis();
			
			String sql = SqlUtil.getInsertSql(operator);
			
			pstmt = conn.prepareStatement(sql);
			
			//操作SQL
			for(Object obj : objs){
				operator = new Operator(obj);
				fieldLis = operator.getFieldLis();
				Param pk = operator.getPrikey();
				if(null != pk.getParamValue() 
						&& !"".equals(pk.getParamValue())){
					if(pk.getParamType().equals("java.lang.Double")){
						pkValue = pk.getParamValue();
					}else{
						pkValue = pk.getParamValue();
					}
				}else {
					//生成主键
					pkValue = UniqueIdUtil.getSID();
				}
				
				pstmt.setObject(1, pkValue);
				
				for(int i=0;i<fieldLis.size();i++){
					Param field = fieldLis.get(i);
				    Object value = field.getParamValue();
				    if(field.getParamType().equals("java.lang.Integer")){
				    	 pstmt.setInt(i+2,Integer.valueOf(value.toString()));
				    }else if(field.getParamType().equals("java.lang.Double")){
				    	 pstmt.setDouble(i+2,Double.valueOf(value.toString()));
				    }else if(field.getParamType().equals("java.util.Date")){
				    	pstmt.setDate(i+2,new java.sql.Date(((java.util.Date)value).getTime()));
				    }else if(field.getParamType().equals("java.sql.Date")){
				    	pstmt.setDate(i+2,(java.sql.Date)value);
				    }else{
				    	pstmt.setObject(i+2,value);
				    }
				}
				
				pstmt.addBatch();
			}
			
			ins = pstmt.executeBatch();
		} catch (SQLException e) {
			throw new Exception("插入数据异常:"+e.getMessage(),e);
		} catch (Exception e) {
			throw new Exception("插入数据异常:"+e.getMessage(),e);
		}  finally {
			try {
				if(null != pstmt){					
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
		
		int result = 0;
		for(int i : ins){
			result += i;
		}
		
		return result;
	}
	
	/**
	 * 更新持久化对象
	 *
	 * @param obj
	 * @return
	 * @throws Exception 
	 */
	public int update(Object obj) throws Exception{
		if(null == obj){
			throw new Exception("对象为空！");
		}
		int up = 0;
		try {
			Operator operator = new Operator(obj);
			List<Param> fieldLis = operator.getFieldLis();
			
			String sql = SqlUtil.getUpdateSql(operator);
			
			pstmt = conn.prepareStatement(sql);
			for(int i=0;i<fieldLis.size();i++){
				Param field = fieldLis.get(i);
			    Object value = field.getParamValue();
			    if("".equals(value)){
			    	value = null;
			    }
			    if(field.getParamType().equals("java.lang.Integer")){
			    	 pstmt.setInt(i+1,Integer.valueOf(value.toString()));
			    }else if(field.getParamType().equals("java.lang.Double")){
			    	 pstmt.setDouble(i+1,Double.valueOf(value.toString()));
			    }else if(field.getParamType().equals("java.util.Date")){
			    	pstmt.setDate(i+1,new java.sql.Date(((java.util.Date)value).getTime()));
			    }else if(field.getParamType().equals("java.sql.Date")){
			    	pstmt.setDate(i+1,(java.sql.Date)value);
			    }else{
			    	pstmt.setObject(i+1,value);
			    }
			}
			up = pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception("更新数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != pstmt){					
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
		
		return up;
	}
	
	/**
	 * 批量更新持久化对象
	 *
	 * @param strSql
	 * @param objs
	 * @throws Exception 
	 */
	public int updateBatch(List<Object> objs) throws Exception{
		if(null == objs || 0 == objs.size()){
			throw new Exception("值列表为空！");
		}
		
		int[] ins = new int[0];
		try {
			for(Object obj : objs){
				Operator operator = new Operator(obj);
				List<Param> fieldLis = operator.getFieldLis();
				
				String sql = SqlUtil.getUpdateSql(operator);
				
				pstmt = conn.prepareStatement(sql);
				for(int j=0;j<fieldLis.size();j++){
					Param field = fieldLis.get(j);
				    Object value = field.getParamValue();
				    if("".equals(value)){
				    	value = null;
				    }
				    if(field.getParamType().equals("java.lang.Integer")){
				    	 pstmt.setInt(j+1,Integer.valueOf(value.toString()));
				    }else if(field.getParamType().equals("java.lang.Double")){
				    	 pstmt.setDouble(j+1,Double.valueOf(value.toString()));
				    }else if(field.getParamType().equals("java.util.Date")){
				    	pstmt.setDate(j+1,new java.sql.Date(((java.util.Date)value).getTime()));
				    }else if(field.getParamType().equals("java.sql.Date")){
				    	pstmt.setDate(j+1,(java.sql.Date)value);
				    }else{
				    	pstmt.setObject(j+1,value);
				    }
				}
				
				pstmt.addBatch();
			}
			
			ins = pstmt.executeBatch();
		} catch (SQLException e) {
			throw new Exception("更新数据异常:"+e.getMessage(),e);
		}
		
		int result = 0;
		for(int i : ins){
			result += i;
		}
		
		return result;
	}
	
	/**
	 * 执行sql语句
	 *
	 * @param sql		数据库操作语句
	 * @param param		参数集合
	 * @return
	 * @throws Exception 
	 */
	public int updateSQL(String sql, List<Object> param) throws Exception{
		if(null == sql || "".equals(sql) || 0 == sql.length()){
			throw new Exception("SQL语句为空！");
		}
		int count = 0;
		try {
			//操作SQL
			pstmt = conn.prepareStatement(sql);
			if(null!=param&&param.size()>0){
				for (int i = 0; i < param.size();i++) {
					pstmt.setObject(i+1, param.get(i));
				}
			}
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception("更新数据:"+e.getMessage(),e);
		} finally {
			try {
				if(null != pstmt){
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
		
		return count;
	}
	
	/**
	 * 批量更新持久化对象
	 *
	 * @param strSql
	 * @param objs
	 * @throws Exception 
	 */
	public int updateSqlBatch(String strSql, List<Object> ... objs) throws Exception{
		if(null == strSql || "".equals(strSql) || 0 == strSql.length()){
			throw new Exception("SQL语句为空！");
		}
		if(null == objs || 0 == objs.length){
			throw new Exception("值列表为空！");
		}
		
		int[] ups = new int[0];
		
		try {
			pstmt = conn.prepareStatement(strSql);
			int len = objs.length;
			for(int i = 0; i < objs[0].size(); i ++){
		    	 for(int j = 0; j < len; j ++){
		    		 pstmt.setObject(j + 1, objs[j].get(i));
		    	 }
		    	 pstmt.addBatch();
			}
			if(len != 0){
				ups = pstmt.executeBatch();
			}
		} catch (SQLException e) {
			throw new Exception("更新数据异常:"+e.getMessage(),e);
		} finally {
			try {
				if(null != pstmt){					
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
		
		int result = 0;
		for(int i = 0;i<ups.length;i++){
			result += ups[i];
		}
		
		return result;
	}
	
	/**
	 * 批量执行sql语句
	 *
	 * @param sqls	数据库操作语句集合
	 * @return
	 * @throws Exception 
	 */
	public int updateSQLBatch(List<String> sqls) throws Exception{
		if(null == sqls || 0 == sqls.size()){
			throw new Exception("SQL语句为空！");
		}
		int[] count;
		try {
			//操作SQL
			stmt = conn.createStatement();
			for (int i = 0; i < sqls.size();i++) {
				stmt.addBatch(sqls.get(i).toString());
			}
			count = stmt.executeBatch();
		} catch (SQLException e) {
			throw new Exception("更新数据:"+e.getMessage(),e);
		} finally {
			try {
				if(null != pstmt){
					pstmt.close();
				}
			} catch (SQLException ignore) {
			}
		}
		int result = 0;
		for(int i = 0;i<count.length;i++){
			result += count[i];
		}
		
		return result;
	}
	
	@Override
	protected void finalize() throws Throwable {
		super.finalize();
		conn.close();
	}
}
